﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using WF_Business;

namespace ExtBusiness.User
{
    public class UserKaoQing
    {
        //保存用户考勤记录
        public static void SaveUserLog(string userID)
        {
            DateTime dt = DateTime.Now;
            string dtShort = DateTime.Now.ToShortDateString();
            if (UserKaoQing.GetWorkTime() != "")
            {
                string strSql = string.Format("select * from XT_TIMEBOOK t where t.userid='{0}' and t.datetimes=to_date('{1}','yyyy-MM-dd')", userID, dtShort);

                DataTable dtTemp = new DataTable();
                SysParams.OAConnection().RunSql(strSql, out dtTemp);

                if (dtTemp.Rows.Count == 0)
                {
                    strSql = string.Format("insert into XT_TIMEBOOK (userid,logintime,datetimes) values ('{0}',to_date('{1}','yyyy-MM-dd hh24:mi:ss'),to_date('{2}','yyyy-MM-dd'))", userID, dt, dtShort);
                    SysParams.OAConnection().RunSql(strSql);
                }
            }
        }
        

        /// <summary>
        /// 返回用户考勤记录
        /// 
        /// </summary>
        /// <param name="whereValue"></param>
        /// <returns></returns>
        public DataTable GetUserLogionLog(string whereValue, string department)
        {
            string strSql = string.Format(@"select c.* from (select (case  when t.datetimes is null then '未考勤' else '已考勤' end) typeName,t.userid,t.datetimes,t.logintime,t.notes,
                        (select user_name from st_user where userid = t.userid) username,
                        (select depart_name from st_department where departid = 
                        (select order_id from st_user_department where userid = t.userid)) departName
                        from XT_TIMEBOOK t where 1 = 1 {0} 
                        union all
                        select (case when 1=1 then '未考勤' else '未考勤' end ) typeName,a.userid,(select datetimes from XT_TIMEBOOK  where 1<>1) datetimes ,
                        (select logintime from XT_TIMEBOOK  where 1<>1) logintime,
                        (select notes from XT_TIMEBOOK  where 1<>1) notes,
                        a.user_name username,
                        (select depart_name from st_department where departid = 
                        (select order_id from st_user_department where userid = a.userid)) departName
                        from st_user a where a.userid not in  (select userid  from XT_TIMEBOOK t
                        where 1 = 1 {0} ) {1} ) c order by c.typeName DESC ,c.departName DESC", whereValue, department);
            strSql = @"select (case
                 when t.datetimes is null then
                  '未考勤'
                 else
                  '已考勤'
               end) typeName,
               t.userid,
               t.datetimes,
               t.logintime,
               t.notes,
               a.user_name,
                b.depart_name
          from XT_TIMEBOOK t,st_user a,st_department b ,st_user_department d
         where 1 = 1 and a.userid=t.userid and b.departid=d.order_id and d.userid=t.userid";
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 返回考勤人数
        /// </summary>
        /// <param name="whereValue"></param>
        /// <returns></returns>
        public string GetKqUserNum(string whereValue)
        {
            string strSql = string.Format("select count(1) from XT_TIMEBOOK t where 1=1 {0}", whereValue);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取工作时间
        /// </summary>
        /// <returns></returns>
        public static String GetWorkTime()
        {
            String dtNow = DateTime.Now.ToShortDateString();
            string strSql = string.Format("select (case when t.mstime is null then t.netime else t.mstime end) worktime from st_worktime t where t.sdate=to_date('{0}','yyyy-MM-dd')", dtNow);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取指定日期的工作时间
        /// 
        /// </summary>
        /// <param name="dataTime"></param>
        /// <returns></returns>
        public static String GetWorkTime(string dataTime)
        {
            string strSql = string.Format("select (case when t.mstime is null then t.netime else t.mstime end) worktime from st_worktime t where t.sdate=to_date('{0}','yyyy-MM-dd')", dataTime);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取首次登陆时间
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public string GetLoginTime(string userid)
        {
            String dtNow = DateTime.Now.ToShortDateString();
            string strSql = string.Format("select logintime from XT_TIMEBOOK t where t.datetimes=to_date('{0}','yyyy-MM-dd') and t.userid='{1}'", dtNow, userid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 更新备注字段
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <param name="netos">备注</param>
        public void UpdateNotes(string userid, string netos)
        {
            string dtNow = DateTime.Now.ToShortDateString();
            string strSql = string.Format("update XT_TIMEBOOK set notes='{0}' where userid='{1}' and datetimes=to_date('{2}','yyyy-MM-dd')", netos, userid, dtNow);
            SysParams.OAConnection().RunSql(strSql);
        }


        /// <summary>
        /// 插入留言信息以及登录时间
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="netos"></param>
        /// <param name="startData"></param>
        /// <param name="endData"></param>
        public void UpdateNotes(string userid, string netos, string startData, string endData)
        {
            string dtNow = DateTime.Now.ToShortDateString();
            string strSql = string.Empty;

            if (startData == endData && dtNow == startData)
            {
                strSql = string.Format("update XT_TIMEBOOK set notes='{0}' where userid='{1}' and datetimes=to_date('{2}','yyyy-MM-dd')", netos, userid, dtNow);
                SysParams.OAConnection().RunSql(strSql);
            }
            else
            {
                DateTime dtStart = Convert.ToDateTime(startData);
                DateTime dtEnd = Convert.ToDateTime(endData).AddDays(1);
                for (DateTime dtTemp = dtStart; dtTemp < dtEnd; dtTemp = dtTemp.AddDays(1))
                {
                    bool HaveLogionTime = HaveTimeInfo(dtTemp.ToShortDateString(), userid);
                    if (HaveLogionTime)
                    {
                        strSql = string.Format("update XT_TIMEBOOK set notes='{0}' where userid='{1}' and datetimes=to_date('{2}','yyyy-MM-dd')", netos, userid, dtTemp.ToShortDateString());
                        SysParams.OAConnection().RunSql(strSql);
                    }
                    else
                    {
                        string UnWorkTime = GetDataUnWorkTime(dtTemp.ToShortDateString());
                        if (!string.IsNullOrEmpty(UnWorkTime.Trim()))//工作日
                        {
                            if (dtTemp.ToShortDateString() != dtNow)
                            {
                                strSql = string.Format(@"insert into XT_TIMEBOOK (USERID,LOGINTIME,DATETIMES,NOTES) values ('{0}',to_date('{1}','yyyy-MM-dd hh24:mi:ss'),to_date('{2}','yyyy-MM-dd'),'{3}')",
                                                    userid, UnWorkTime, dtTemp.ToShortDateString(), netos);
                                SysParams.OAConnection().RunSql(strSql);
                            }
                        }
                    }
                }
            }
        }
        //查看是否有登录
        private bool HaveTimeInfo(string datetime, string userid)
        {
            string strSql = string.Format("select * from XT_TIMEBOOK t where t.userid='{0}' and t.datetimes=to_date('{1}','yyyy-MM-dd')", userid, datetime);
            DataTable dt = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dt);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //获取下班时间 如果返回为空 则 当天为非工作日
        private string GetDataUnWorkTime(string datatime)
        {
            string strSql = string.Format("select * from st_worktime t where t.sdate=to_date('{0}','yyyy-MM-dd')", datatime);
            DataTable dt = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dt);
            if (dt.Rows.Count > 0)
            {
                if (string.IsNullOrEmpty(dt.Rows[0]["netime"].ToString()))
                {
                    return dt.Rows[0]["metime"].ToString();
                }
                else
                {
                    return dt.Rows[0]["netime"].ToString();
                }
            }
            else
            {
                return string.Empty;
            }
        }


        /// <summary>
        /// 查看考勤表中备注字段是否为空
        /// </summary>
        /// <param name="userid"></param>
        /// <returns>如果空返回0，否则返回1</returns>
        public String NotesIsNotNull(string userid)
        {
            string dtNow = DateTime.Now.ToShortDateString();
            string strSql = string.Format("select notes from XT_TIMEBOOK where userid='{0}' and datetimes=to_date('{1}','yyyy-MM-dd')", userid, dtNow);
            string sNotes = SysParams.OAConnection().GetValue(strSql);
            string i = "0";
            if (sNotes != "")
            {
                i = "1";
            }
            return i;

        }
        
    }
}
